--    Program: 		CAPCMEXT_CE_STATEMENT_INTERFACE.sql
--    Description:	Extract CM Statement Header & Line interface information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   

  l_processed_count_1 	number;
  l_rejected_count_1  	number;
  l_processed_count_2	number;
  l_rejected_count_2	number;

  l_err_msg       	varchar2(2000);
  
  l_utl_file_1  		utl_file.file_type;
  l_utl_file_2 		utl_file.file_type;

  l_filename_1 		varchar2(255);
  l_filename_2 		varchar2(255);

  l_pathname  		varchar2(100);

  l_ngo_sob			varchar2(20);
  l_temp			varchar2(10);

  l_bg_code			varchar2(10);

  cursor cur_1 (p_bg_code varchar2) is
    select cshia.statement_number, 
	   cshia.bank_account_num, 
	   cshia.statement_date, 
	   cshia.bank_name, 
	   cshia.bank_branch_name, 
	   cshia.control_begin_balance, 
	   cshia.control_total_dr,
	   cshia.control_total_cr, 
	   cshia.control_end_balance, 
	   cshia.control_dr_line_count, 
	   cshia.control_cr_line_count, 
	   cshia.control_line_count, 
	   cshia.record_status_flag, 
	   cshia.currency_code, 
	   cshia.created_by, 
	   cshia.creation_date, 
	   cshia.attribute_category, 
	   cshia.attribute1, 
	   cshia.attribute2, 
	   cshia.attribute3, 
	   cshia.attribute4, 
	   cshia.attribute5, 
	   cshia.attribute6, 
	   cshia.attribute7, 
	   cshia.attribute8, 
	   cshia.attribute9, 
	   cshia.attribute10,
	   cshia.attribute11, 
	   cshia.attribute12, 
	   cshia.attribute13, 
	   cshia.attribute14, 
	   cshia.attribute15, 
	   cshia.last_updated_by, 
	   cshia.last_update_date, 
	   cshia.org_id, 
	   cshia.check_digits, 
	   cshia.cashflow_balance, 
	   cshia.int_calc_balance,
         cshia.intra_day_flag
from ce_statement_headers_int_all cshia, hr_all_organization_units haou
where cshia.org_id = haou.organization_id
and haou.name like 'OU_'||P_BG_CODE||'%'
order by cshia.rowid;


cursor cur_2 (p_bg_code varchar2) is
select csli.bank_account_num,
	   csli.statement_number, 
	   csli.line_number, 
	   csli.trx_date,
	   csli.trx_code,
	   csli.effective_date,
	   csli.trx_text,
	   csli.invoice_text, 
	   csli.bank_account_text, 
	   csli.amount,
	   csli.currency_code, 
	   csli.user_exchange_rate_type, 
	   csli.exchange_rate_date, 
	   csli.exchange_rate,
	   csli.original_amount, 
	   csli.charges_amount,
         csli.bank_trx_number,
	   csli.customer_text,
	   csli.created_by,
	   csli.creation_date,
	   csli.last_updated_by,
	   csli.last_update_date,
	   csli.attribute_category,
	   csli.attribute1, 
	   csli.attribute2, 
	   csli.attribute3, 
	   csli.attribute4, 
	   csli.attribute5, 
	   csli.attribute6, 
	   csli.attribute7, 
	   csli.attribute8, 
	   csli.attribute9, 
	   csli.attribute10,
	   csli.attribute11, 
	   csli.attribute12, 
	   csli.attribute13, 
	   csli.attribute14, 
	   csli.attribute15
	   from ce_statement_lines_interface csli, ce_statement_headers_int_all cshia, hr_all_organization_units haou
where cshia.org_id = haou.organization_id
and cshia.statement_number = csli.statement_number
and cshia.bank_account_num = csli.bank_account_num
and haou.name like 'OU_'||P_BG_CODE||'%'
order by cshia.rowid, csli.rowid;


begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPCM_CE_STATEMENT_HEADERS_INT_ALL.dat';
  l_filename_2 := 'CAPCM_CE_STATEMENT_LINES_INTERFACE.dat';
 
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);

  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;
                                       
  l_ngo_sob := '&1';
  l_bg_code := substr(l_ngo_sob,(instr(l_ngo_sob,'C')),4);

  begin
 	select distinct substr(name,4,4) into l_temp
	 	from hr_all_organization_units
	 	where name like 'OU_'|| l_bg_code ||'%';

   exception
     when others then
       dbms_output.put_line(l_bg_code ||': BG name not found');
       raise;
   end;    
  
    for cur1 in cur_1(l_bg_code) loop   	
     exit when cur_1%NOTFOUND;
     -- output data
     utl_file.put_line( l_utl_file_1, 
	  			cur1.statement_number|| chr(9) ||
				cur1.bank_account_num || chr(9) ||
				cur1.statement_date || chr(9) ||
	  			cur1.bank_name || chr(9) ||
	  			cur1.bank_branch_name || chr(9) ||
	  			cur1.control_begin_balance || chr(9) ||
	  			cur1.control_total_dr || chr(9) ||
	  			cur1.control_total_cr || chr(9) ||
	  			cur1.control_end_balance || chr(9) ||
	  			cur1.control_dr_line_count || chr(9) ||
	  			cur1.control_cr_line_count || chr(9) ||
	  			cur1.record_status_flag || chr(9) ||
	  			cur1.currency_code || chr(9) ||
	  			cur1.created_by || chr(9) ||
	  			cur1.creation_date || chr(9) ||
	  			cur1.attribute_category || chr(9) ||
	  			cur1.attribute1 || chr(9) ||              
	  			cur1.attribute2 || chr(9) ||          
	  			cur1.attribute3 || chr(9) ||          
	  			cur1.attribute4 || chr(9) ||                              
	  			cur1.attribute5 || chr(9) ||                  
	  	  		cur1.attribute6 || chr(9) ||                  
	  			cur1.attribute7 || chr(9) ||                  
	  			cur1.attribute8 || chr(9) ||                  
	  			cur1.attribute9 || chr(9) ||                  
	  			cur1.ATTRIBUTE10 || chr(9) ||                  
	  			cur1.ATTRIBUTE11 || chr(9) ||                 
	  			cur1.ATTRIBUTE12 || chr(9) ||                 
	  			cur1.ATTRIBUTE13 || chr(9) ||                 
	  			cur1.ATTRIBUTE14 || chr(9) ||                 
	  			cur1.ATTRIBUTE15 || chr(9) ||                 
	  			cur1.last_updated_by || chr(9) ||
				cur1.last_update_date || chr(9) ||
	  			cur1.org_id || chr(9) ||
	  			cur1.check_digits|| chr(9) ||
				cur1.cashflow_balance|| chr(9) ||
	  			cur1.int_calc_balance || chr(9) ||
				cur1.intra_day_flag
	    );

  	    l_processed_count_1 := l_processed_count_1 + 1;
       end loop;
     
	 for cur2 in cur_2(l_bg_code) loop
	 	exit when cur_2%NOTFOUND;
	 	  utl_file.put_line( l_utl_file_2,
		  			   cur2.bank_account_num || chr(9) ||
					   cur2.statement_number || chr(9) ||
					   cur2.line_number || chr(9) ||
					   cur2.trx_date || chr(9) ||
					   cur2.trx_code|| chr(9) ||
					   cur2.effective_date || chr(9) ||
					   cur2.trx_text || chr(9) ||
					   cur2.invoice_text || chr(9) ||
					   cur2.bank_account_text || chr(9) ||
					   cur2.amount || chr(9) ||
					   cur2.currency_code || chr(9) ||
					   cur2.user_exchange_rate_type || chr(9) ||
					   cur2.exchange_rate_date || chr(9) ||
					   cur2.exchange_rate || chr(9) ||
					   cur2.original_amount || chr(9) ||
					   cur2.charges_amount || chr(9) ||
					   cur2.bank_trx_number || chr(9) ||
					   cur2.customer_text || chr(9) ||
					   cur2.created_by || chr(9) ||
					   cur2.creation_date || chr(9) ||
					   cur2.last_updated_by || chr(9) ||
					   cur2.last_update_date || chr(9) ||
					   cur2.attribute_category || chr(9) ||
					   cur2.attribute1 || chr(9) ||
					   cur2.attribute2 || chr(9) ||
					   cur2.attribute3 || chr(9) ||
					   cur2.attribute4 || chr(9) ||
					   cur2.attribute5 || chr(9) ||
					   cur2.attribute6 || chr(9) ||
					   cur2.attribute7 || chr(9) ||
					   cur2.attribute8 || chr(9) ||
					   cur2.attribute9 || chr(9) ||
					   cur2.attribute10 || chr(9) ||
					   cur2.attribute11 || chr(9) ||
					   cur2.attribute12 || chr(9) ||
					   cur2.attribute13 || chr(9) ||
					   cur2.attribute14 || chr(9) ||
					   cur2.attribute15
		  ); 
		  
		  l_processed_count_2 := l_processed_count_2 + 1;
  	  end loop;
  	    
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_bg_code || ': bank statement header int all not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPCMEXT_CE_STATEMENT_HEADERS_INT_ALL', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
   
   commit;
   
   l_err_msg := '';
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_bg_code || ': bank statement line interface not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPCMEXT_CE_STATEMENT_LINES_INTERFACE', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
   
   commit;

  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);

exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
